# Define your item pipelines here
#
# Don't forget to add your pipeline to the ITEM_PIPELINES setting
# See: https://docs.scrapy.org/en/latest/topics/item-pipeline.html


# useful for handling different item types with a single interface
from itemadapter import ItemAdapter

import pymysql
from twisted.enterprise import adbapi
from pymysql import cursors
from jianshu_spider.utils.common import extract_num,extract_qian_num


# 同步入库
class JianshuSpiderPipeline(object):
    def __init__(self):
        dbparams = {
            'host': '127.0.0.1',
            'port': 3306,
            'user': 'root',
            'password': '123456',
            'database': 'jianshu',
            'charset': 'utf8'
        }
        self.conn = pymysql.connect(**dbparams)
        self.cursor = self.conn.cursor()
        self._sql = None

    def process_item(self, item, spider):
        like_count = int(extract_num(item['like_count']))
        comment_count = int(extract_num(item['comment_count']))
        read_count = int(extract_qian_num(item['read_count']))
        word_count = int(extract_qian_num(item['word_count']))
        params = (
            item['title'], item['content'], item['author'], item['avatar'], item['pub_time'], item['origin_url'],
            item['article_id'],read_count, like_count, word_count, item['subjects'], comment_count
        )

        self.cursor.execute(self.sql, params)
        self.conn.commit()
        return item

    @property
    def sql(self):
        if not self._sql:
            self._sql = """
                          insert into article(id,title,content,author,avatar,pub_time,origin_url,article_id,read_count,like_count,word_count,subjects,comment_count) values (null,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)
                          """
            return self._sql
        return self._sql


# 异步入库
class JianshuTwistedPipeline(object):
    def __init__(self):
        dbparams = {
            'host': '127.0.0.1',
            'port': 3306,
            'user': 'root',
            'password': '123456',
            'database': 'jianshu',
            'charset': 'utf8',
            'cursorclass': cursors.DictCursor
        }
        self.dbpool = adbapi.ConnectionPool('pymysql', **dbparams)
        self._sql = None

    @property
    def sql(self):
        if not self._sql:
            self._sql = """
                insert into article(id,title,content,author,avatar,pub_time,origin_url,article_id,read_count,like_count,word_count,subjects,comment_count) values (null,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)
                """
            return self._sql
        return self._sql

    def process_item(self, item, spider):
        defer = self.dbpool.runInteraction(self.insert_item, item)
        defer.addErrback(self.handle_error, item, spider)

    def insert_item(self, cursor, item):
        like_count = extract_num(item['like_count'])
        comment_count = extract_num(item['comment_count'])
        read_count = extract_num(item['read_count'])
        word_count = extract_num(item['word_count'])
        cursor.execute(self.sql, (
            item['title'],
            item['content'],
            item['author'],
            item['avatar'],
            item['pub_time'],
            item['origin_url'],
            item['article_id'],
            like_count,
            comment_count,
            read_count,
            word_count,
            item['subjects']
        ))

    def handle_error(self, error, item, spider):
        print('=' * 10 + "error" + '=' * 10)
        print(error)
        print('=' * 10 + "error" + '=' * 10)
